IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MD3.RunFKConstraints'))
BEGIN
	DROP PROCEDURE MD3.RunFKConstraints
END
GO

CREATE Procedure MD3.RunFKConstraints
AS
    
BEGIN

    SET NOCOUNT ON  

	/*
		Runs all FK Constraints found in MD3.FKConstraints
		this is called from/in the ForeignKeys folder, after the MD3.FKConstraints table is loaded

	*/

	

DECLARE 
			@ForeignKeyName		SYSNAME ,   
			@SchemaName			SYSNAME , 
			@TableName			SYSNAME ,
			@KeyColumns			VARCHAR(MAX),
			@ParentSchemaName	SYSNAME , 
			@ParentTableName		SYSNAME ,
			@ParentKeyColumns	VARCHAR(MAX),
			@IsNotTrusted        BIT ,
			@IsDeleteCascade     BIT ,
			@IsUpdateCascade     BIT , 
			@DropFK				BIT 
	
DECLARE CurIndexes CURSOR FOR 	
	--get a list of FKs
	SELECT 
			ForeignKeyName		,
			SchemaName			,
			TableName			,
			KeyColumns			,
			ParentSchemaName	,
			ParentTableName		,
			ParentKeyColumns	,
			IsNotTrusted        ,
			IsDeleteCascade     ,
			IsUpdateCascade     ,
			DropFK				
	FROM MD3.FKConstraints
	ORDER BY Id
OPEN CurIndexes
FETCH NEXT FROM curIndexes INTO 
			@ForeignKeyName		,
			@SchemaName			,
			@TableName			,
			@KeyColumns			,
			@ParentSchemaName	,
			@ParentTableName	,
			@ParentKeyColumns	,
			@IsNotTrusted       ,
			@IsDeleteCascade    ,
			@IsUpdateCascade    ,
			@DropFK				
WHILE @@FETCH_STATUS = 0
BEGIN 

	EXEC MD3.CreateForeignKey
		@ForeignKeyName	=	@ForeignKeyName,
		@SchemaName		=	@SchemaName,
		@TableName		=	@TableName,
		@KeyColumns		=	@KeyColumns,
		@ParentSchemaName=	@ParentSchemaName,
		@ParentTableName=	@ParentTableName,
		@ParentKeyColumns=	@ParentKeyColumns,
		@AllowNoCheck    =   @IsNotTrusted,
		@IsDelCascade  =  @IsDeleteCascade,
		@IsUpdCascade  =  @IsUpdateCascade,
		@DropFK			=	@DropFK
	;

	
	
	FETCH NEXT FROM curIndexes INTO 
				@ForeignKeyName		,
				@SchemaName			,
				@TableName			,
				@KeyColumns			,
				@ParentSchemaName	,
				@ParentTableName	,
				@ParentKeyColumns	,
				@IsNotTrusted       ,
				@IsDeleteCascade    ,
				@IsUpdateCascade    ,
				@DropFK				
END

CLOSE curIndexes
DEALLOCATE curIndexes



END
GO
